-- Migration: Add BSC tables
-- Created: 2025-01-17

-- BSC tokens table
CREATE TABLE IF NOT EXISTS bsc_tokens (
    id VARCHAR(255) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    launch_id INTEGER UNIQUE NOT NULL,
    token_address VARCHAR(42) DEFAULT '',
    creator VARCHAR(42) NOT NULL,
    name VARCHAR(255) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    website VARCHAR(255),
    telegram VARCHAR(255),
    twitter VARCHAR(255),
    image_url VARCHAR(500),
    target_amount VARCHAR(50) NOT NULL, -- BNB amount as string
    raised_amount VARCHAR(50) DEFAULT '0',
    token_price VARCHAR(50) NOT NULL, -- BNB per token as string
    created_at INTEGER NOT NULL,
    deadline INTEGER NOT NULL,
    launched BOOLEAN DEFAULT FALSE,
    cancelled BOOLEAN DEFAULT FALSE,
    current_price VARCHAR(50), -- Current market price in BNB
    price_change_24h DECIMAL(10,4), -- 24h price change percentage
    volume_24h VARCHAR(50), -- 24h trading volume in BNB
    market_cap VARCHAR(50), -- Current market cap in BNB
    holders INTEGER DEFAULT 0,
    tokens_sold VARCHAR(50) DEFAULT '0', -- Total tokens sold
    total_supply_for_sale VARCHAR(50) DEFAULT '800000000000000000000000000', -- 800M tokens (with 18 decimals)
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- BSC contributions/transactions table
CREATE TABLE IF NOT EXISTS bsc_contributions (
    id VARCHAR(255) PRIMARY KEY DEFAULT gen_random_uuid()::text,
    launch_id INTEGER NOT NULL,
    contributor VARCHAR(42) NOT NULL,
    amount VARCHAR(50) NOT NULL, -- BNB amount contributed/received
    tokens_received VARCHAR(50), -- Tokens received (for buy transactions)
    tokens_sold VARCHAR(50), -- Tokens sold (for sell transactions)
    trade_type VARCHAR(10) NOT NULL CHECK (trade_type IN ('buy', 'sell')),
    tx_hash VARCHAR(66) DEFAULT '',
    timestamp INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (launch_id) REFERENCES bsc_tokens(launch_id) ON DELETE CASCADE
);

-- BSC price history table
CREATE TABLE IF NOT EXISTS bsc_price_history (
    id SERIAL PRIMARY KEY,
    launch_id INTEGER NOT NULL,
    price VARCHAR(50) NOT NULL, -- Price in BNB
    volume VARCHAR(50) DEFAULT '0', -- Volume in BNB
    market_cap VARCHAR(50) DEFAULT '0', -- Market cap in BNB
    timestamp INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (launch_id) REFERENCES bsc_tokens(launch_id) ON DELETE CASCADE
);

-- BSC token holders table
CREATE TABLE IF NOT EXISTS bsc_token_holders (
    id SERIAL PRIMARY KEY,
    launch_id INTEGER NOT NULL,
    holder_address VARCHAR(42) NOT NULL,
    token_balance VARCHAR(50) NOT NULL, -- Token balance
    bnb_contributed VARCHAR(50) DEFAULT '0', -- Total BNB contributed
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (launch_id) REFERENCES bsc_tokens(launch_id) ON DELETE CASCADE,
    UNIQUE(launch_id, holder_address)
);

-- BSC graduated tokens table (for tracking DEX listings)
CREATE TABLE IF NOT EXISTS bsc_graduated_tokens (
    id SERIAL PRIMARY KEY,
    launch_id INTEGER NOT NULL,
    token_address VARCHAR(42) NOT NULL,
    pancakeswap_pair VARCHAR(42), -- PancakeSwap pair address
    liquidity_bnb VARCHAR(50), -- BNB used for liquidity
    liquidity_tokens VARCHAR(50), -- Tokens used for liquidity
    creator_fee VARCHAR(50), -- Fee paid to creator
    graduated_at INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (launch_id) REFERENCES bsc_tokens(launch_id) ON DELETE CASCADE
);

-- Indexes for better performance
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_creator ON bsc_tokens(creator);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_created_at ON bsc_tokens(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_launched ON bsc_tokens(launched);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_cancelled ON bsc_tokens(cancelled);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_deadline ON bsc_tokens(deadline);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_raised_amount ON bsc_tokens(raised_amount);
CREATE INDEX IF NOT EXISTS idx_bsc_tokens_search ON bsc_tokens USING gin(to_tsvector('english', name || ' ' || symbol || ' ' || description));

CREATE INDEX IF NOT EXISTS idx_bsc_contributions_launch_id ON bsc_contributions(launch_id);
CREATE INDEX IF NOT EXISTS idx_bsc_contributions_contributor ON bsc_contributions(contributor);
CREATE INDEX IF NOT EXISTS idx_bsc_contributions_timestamp ON bsc_contributions(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_bsc_contributions_trade_type ON bsc_contributions(trade_type);

CREATE INDEX IF NOT EXISTS idx_bsc_price_history_launch_id ON bsc_price_history(launch_id);
CREATE INDEX IF NOT EXISTS idx_bsc_price_history_timestamp ON bsc_price_history(timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_bsc_token_holders_launch_id ON bsc_token_holders(launch_id);
CREATE INDEX IF NOT EXISTS idx_bsc_token_holders_address ON bsc_token_holders(holder_address);

CREATE INDEX IF NOT EXISTS idx_bsc_graduated_launch_id ON bsc_graduated_tokens(launch_id);
CREATE INDEX IF NOT EXISTS idx_bsc_graduated_token_address ON bsc_graduated_tokens(token_address);

-- Trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_bsc_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_bsc_tokens_updated_at BEFORE UPDATE ON bsc_tokens FOR EACH ROW EXECUTE PROCEDURE update_bsc_updated_at_column();
CREATE TRIGGER update_bsc_token_holders_updated_at BEFORE UPDATE ON bsc_token_holders FOR EACH ROW EXECUTE PROCEDURE update_bsc_updated_at_column();

-- Insert some sample data for testing (optional)
-- INSERT INTO bsc_tokens (
--     launch_id, creator, name, symbol, description, target_amount, token_price, 
--     created_at, deadline
-- ) VALUES (
--     0, '0x742d35cc6491c54e6c8e2a6b89cd3db7a3c4d7cc', 'Test BSC Token', 'TBT', 
--     'A test token for BSC LaunchPad', '10000000000000000000', '100000000000000', 
--     EXTRACT(EPOCH FROM NOW()), EXTRACT(EPOCH FROM NOW()) + 604800
-- );
